"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 导入导出、备份恢复工具支持M*兼容性
Case Name   : 存储过程中调用日期处理函数，使用gs_dump导出后再执行导入
Create At   : 2023.05.04
Owner       : z@wan005
Description :
    1.创建兼容b库
    2.兼容b库创建测试用户并授权
    3.兼容b库创建存储过程，存储体使用时间类型函数
    4.使用gs_dump工具导出表为纯文本格式
    5.导入之前导出的数据到新库
    6.连接导入库查询并调用存储过程
    7.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
History     :
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class GsDump0020(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.dbuser_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.com = Common()
        self.db_name = "db_dump_0020"
        self.db_name_01 = "db_dump_0020_01"
        self.pro_name = "pro_dump_0020"
        self.user_name = "user_dump0020"
        self.dump_path = os.path.join(macro.DB_BACKUP_PATH, 'dump0020')

    def test_server_tools(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '----step1: 创建兼容b库 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} encoding 'UTF8' " \
                  f"dbcompatibility 'b';" \
                  f"drop database if exists {self.db_name_01};" \
                  f"create database {self.db_name_01} encoding 'UTF8' " \
                  f"dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertEqual(2, sql_result.count(
            self.constant.CREATE_DATABASE_SUCCESS), '执行失败:' + text)

        text = '---step2: 兼容b库创建测试用户并授权 expect: 成功---'
        self.log.info(text)
        sql_cmd = f'''drop user if exists {self.user_name} cascade;
        create user {self.user_name} password '{macro.PASSWD_INITIAL}';\
        grant proxy on current_user() to {self.user_name};'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.GRANT_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step3: 兼容b库创建存储过程，存储体使用日期类型函数 expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''drop procedure if exists {self.pro_name};
        create definer = {self.user_name} procedure {self.pro_name}\
        (
        curDATE out date,
        current_time1 out time,
        current_time2 out time,
        current_time3 out time,
        time1 out time,
        time2 out time,
        time3 out time,
        curtime1 out time,
        curtime2 out time,
        datetime1 out datetime,
        datetime2 out datetime,
        datetime3 out datetime,
        localtime1 out datetime,
        localtime2 out datetime,
        localtime3 out datetime,
        datetime_1 out datetime,
        datetime_2 out datetime,
        datetime_3 out datetime,
        now1 out datetime,
        now2 out datetime,
        sys1 out datetime,
        sys2 out datetime,
        date1 out text,
        day out integer,
        dayname out text,
        lastday out date,
        month out integer,
        monthname out text,
        seconds out integer,
        week1 out integer,
        week2 out integer,
        yearweek1 out bigint,
        yearweek2 out bigint,
        adddate1 out datetime,
        adddate2 out time,
        adddate3 out date,
        adddate4 out datetime,
        addtime out text,
        convert_tz out datetime,
        dateadd1 out date,
        dateadd2 out datetime,
        dateadd3 out datetime,
        date_sub1 out date,
        date_sub2 out datetime,
        date_sub3 out datetime,
        datediff out integer,
        from_days out date,
        from_unixtime out date,
        str_to_date out date,
        a out text,
        b out text
    )
    IS
        BEGIN
            select curdate() into curdate;
            select current_time into current_time1;
            select current_time() into current_time2;
            select current_time(3) into current_time3;
            select current_time into time1;
            select current_time() into time2;
            select current_time(3) into time3;
            select curtime() into curtime1;
            select curtime(3) into curtime2;
            select current_timestamp into datetime1;
            select current_timestamp() into datetime2;
            select current_timestamp(3) into datetime3;
            select localtime into localtime1;
            select localtime() into localtime2;
            select localtime(3) into localtime3;
            select localtimestamp into datetime_1;
            select localtimestamp() into datetime_2;
            select localtimestamp(3) into datetime_3;
            select now() into now1;
            select now(3) into now2;
            select sysdate() into sys1;
            select sysdate(3) into sys2;
            select date('2021-11-12') into date1;
            select day('2021-11-12') into day;
            select dayname('2000-1-1') into dayname;
            set dolphin.b_compatibility_mode = true;
            select last_day('2021-1-30') into lastday;
            reset dolphin.b_compatibility_mode;
            select month('2021-11-12') into month;
            select monthname('2000-1-1') into monthname;
            select time_to_sec('838:59:59') into seconds;
            select week('2000-1-1') into week1;
            select week('2000-1-1', 2) into week2;
            select yearweek('1987-01-01') into yearweek1;
            select yearweek('1987-01-01', 0) into yearweek2;
            select adddate('2021-11-12', interval 1 second) into adddate1;
            select adddate(time'12:12:12', interval 1 day) into adddate2;
            select adddate('2021-11-12', 1) into adddate3;
            select adddate(time'12:12:12', 1) into adddate4;
            select addtime('11:22:33', '10:20:30') into addtime;
            select convert_tz('2004-01-01 12:00:00', 'GMT', 'MET') into \
            convert_tz;
            select date_add('2022-01-01', interval 31 day) into dateadd1;
            select date_add('2022-01-01 01:01:01', interval 1 year) into \
            dateadd2;
            select date_add('2022-01-01', interval 1 second) into dateadd3;
            select date_sub('2022-01-01', interval 31 day) into date_sub1;
            select date_sub('2022-01-01 01:01:01', interval 1 year) into \
            date_sub2;
            select date_sub('2022-01-01', interval 1 second) into date_sub3;
            select datediff('2001-01-01', '321-02-02') into datediff;
            select from_days(1000) into from_days;
            select from_unixtime(1) into from_unixtime;
            select str_to_date('01,5,2013','%d,%m,%Y') into str_to_date;
            select get_format(datetime,'iso') into a;
            set dolphin.b_compatibility_mode=on;
            select extract(year from '2021-11-12 12:12:12.000123') into b;
            reset dolphin.b_compatibility_mode;
        END;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '----step4: 使用gs_dump工具导出表为纯文本格式 expect: 成功----'
        self.log.info(text)
        gs_dump_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gs_dump " \
                      f"-p {self.dbuser_node.db_port} " \
                      f"{self.db_name} -F p " \
                      f"-f {self.dump_path}"
        self.log.info(gs_dump_cmd)
        dump_msg = self.dbuser_node.sh(gs_dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(f"dump database {self.db_name} successfully",
                      dump_msg, '执行失败:' + text)

        text = '----step5: 导入之前导出的数据到新库 expect: 成功----'
        self.log.info(text)
        restore_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gsql -p {self.dbuser_node.db_port} " \
                      f"{self.db_name_01} -r -f {self.dump_path}"
        self.log.info(restore_cmd)
        restore_msg = self.dbuser_node.sh(restore_cmd).result()
        self.log.info(restore_msg)
        self.assertEqual(1, restore_msg.count('CREATE PROCEDURE'),
                         '执行失败:' + text)

        text = '-step6: 连接导入库查询并调用存储过程 expect: 成功--'
        self.log.info(text)
        sql_cmd = f"show create procedure {self.pro_name};" \
                  f"call {self.pro_name}(curDATE,current_time1," \
                  f"current_time2,current_time3,time1,time2,time3,curtime1," \
                  f"curtime2,datetime1,datetime2,datetime3,localtime1," \
                  f"localtime2,localtime3,datetime_1,datetime_2,datetime_3," \
                  f"now1,now2,sys1,sys2,date1,day,dayname,lastday,month," \
                  f"monthname,seconds,week1,week2,yearweek1,yearweek2," \
                  f"adddate1,adddate2,adddate3,adddate4,addtime,convert_tz," \
                  f"dateadd1,dateadd2,dateadd3,date_sub1,date_sub2," \
                  f"date_sub3,datediff,from_days,from_unixtime,str_to_date" \
                  f",a,b);"

        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name_01)
        self.log.info(sql_result)
        self.assertIn(f'CREATE DEFINER = {self.user_name} PROCEDURE',
                      sql_result, '执行失败:' + text)
        self.assertIn('(1 row)', sql_result, '执行失败:' + text)
        self.assertIn('| %Y-%m-%d %H:%i:%s | 2021', sql_result)

    def tearDown(self):
        text = '---step7: 清理环境 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name_01};" \
                  f"drop user if exists {self.user_name} cascade;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname='postgres')
        self.log.info(sql_result)
        rm_cmd = f"rm -rf {self.dump_path}"
        self.log.info(rm_cmd)
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)
        self.assertEqual(2,
                         sql_result.count(self.constant.DROP_DATABASE_SUCCESS))
        self.assertEqual('', rm_msg)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
